package admin.service.member;

import java.util.List;
import java.util.Map;

import teacher.bean.Teacher;
import database.dao.MysqlCrud;

public class LeaderServiceAdmin {
	
	/**
	 * @category 统计一个方向的所有专家人数
	 * @ author laohe 
	 */
	public int getCountOfExpert(Teacher teacher) {
		int sum=0;
		String sql="select count(*) from expertclass as acheve ,teacher as T where acheve.expId = T.teaId and T.direction=?";
		String[] parameter={String.valueOf(teacher.getDirection())};
		MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> lists=mysqlcurd.SelectData(sql,parameter);
		Map map=lists.get(0);
		sum=Integer.parseInt(map.get(0).toString());
		return sum;
	}
	/**
	 * @category 统计一个方向所有的老师姓名
	 * @ author laohe
	 * 输入参数为含有老师反向的对象，返回值为一个List
	 */
	public List<Map> getDirectionName(Teacher teacher)
	{
		String sql="select * from teacher where teacher.direction=?";
		String[] parameter ={String.valueOf(teacher.getDirection())};
		MysqlCrud mysqlcrud=new MysqlCrud();
		List<Map> list=mysqlcrud.SelectData(sql, parameter);
		return list;
		/*for(int i=0;i<lists.size();i++)
		{
			Map map=lists.get(i);
			System.out.println(map.get(0));
		}
		*/
		/*return lists;*/
	}
	
	
	/**
	 * @category 根据姓名查工号
	 * @ author laohe
	 * 输入参数为含有老师反向的对象，返回值为一个List
	 */
	public List<Map> getUserFromName(Teacher teacher)
	{
		String sql="select * from teacher where teacher.name=?";
		String[] parameter ={String.valueOf(teacher.getName())};
		MysqlCrud mysqlcrud=new MysqlCrud();
		List<Map> list=mysqlcrud.SelectData(sql, parameter);
		return list;
		/*for(int i=0;i<lists.size();i++)
		{
			Map map=lists.get(i);
			System.out.println(map.get(0));
		}
		*/
		/*return lists;*/
	}
	
	/**
	 * @category 得到一个方向专家类别（1,2,3,4....21）的人数
	 * @author 老贺
	 * @return int
	 */
	public int getCountOfExpertClass(int i,Teacher teacher)
	{
		int sum=0;
		String sql="select count(*) from expertclass as pa,teacher as t where pa.expId = t.teaId and pa.expert=? and t.direction =?";
		String[] parameter= {String.valueOf(i),String.valueOf(teacher.getDirection())};
		MysqlCrud mysqlcurd = new MysqlCrud();
		List<Map> lists=mysqlcurd.SelectData(sql,parameter);
		Map map=lists.get(0);
		sum=Integer.parseInt(map.get(0).toString());		
		return sum;
		
	}
	
	

	/**
	 * @category 得到一个方向的所有获奖的数量
	 * @author chenkai
	 * @return sum int 教师数量
	 */
	public int getCountOfDirectAward(Teacher teacher) {
		int sum=0;
		String sql="select count(*) from award as acheve ,teacher as T where acheve.teaId = T.teaId and acheve.state>4 and T.direction=?";
		String[] parameter={String.valueOf(teacher.getDirection())};
		MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> lists=mysqlcurd.SelectData(sql,parameter);
		Map map=lists.get(0);
		sum=Integer.parseInt(map.get(0).toString());
		return sum;
	}
	
	
	/** 
	 * @category 得到一个方向获奖级别（1,2,3）的总数量
	 * @author 老贺
	 */
	
	
	public int getCountOfAwardInclude(int i,Teacher teacher)
	{
		int sum=0;
		String sql="select count(*) from award as aw,teacher as t where aw.teaId = t.teaId and aw.level=? and aw.state>4 and t.direction =?";
		String[] parameter= {String.valueOf(i),String.valueOf(teacher.getDirection())};
		MysqlCrud mysqlcurd = new MysqlCrud();
		List<Map> lists=mysqlcurd.SelectData(sql,parameter);
		Map map=lists.get(0);
		sum=Integer.parseInt(map.get(0).toString());		
		return sum;
	}
	
	/**
	 * @category 得到一个方向的所有  纵向项目等级为？（1,2,3）的数量
	 * @author laohe
	 * @return sum int 
	 */
	public int getCountOfProjectClass0(int i,Teacher teacher)
	{
		int sum=0;
	      String sql = "select count(*) from project as pr,teacher as t where pr.teaId=t.teaId and pr.level=? and pr.state>4 and pr.kind=0 and  t.direction=?";
		String[] parameter ={ String.valueOf(i),String.valueOf(teacher.getDirection())};
		MysqlCrud mysqlcrud =new MysqlCrud();	
		List<Map> lists=mysqlcrud.SelectData(sql, parameter);
		 Map map = lists.get(0);
		 try {
			sum=Integer.parseInt(map.get(0).toString());
		} catch (NumberFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		return sum;	
	}	
	
	/**横向项目等级为0.1.2时数量
	 * 
	 * @param i
	 * @param teacher
	 * @return
	 */
	public int getCountOfProjectClass1(int i,Teacher teacher)
	{
		int sum=0;
	      String sql = "select count(*) from project as pr,teacher as t where pr.teaId=t.teaId and pr.class=? and pr.kind=1 and pr.state>4 and t.direction=?";
		String[] parameter ={ String.valueOf(i),String.valueOf(teacher.getDirection())};
		MysqlCrud mysqlcrud =new MysqlCrud();	
		List<Map> lists=mysqlcrud.SelectData(sql, parameter);
		 Map map = lists.get(0);
		 try {
			sum=Integer.parseInt(map.get(0).toString());
		} catch (NumberFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		return sum;	
	}	
	/**
	 * @category 得到一个方向的所有项目的数量
	 * @author chenkai
	 * @return sum int 教师数量
	 */
	public int getCountOfDirectProject(Teacher teacher){
		int sum=0;
		String sql="select count(*) from project as acheve ,teacher as T where acheve.teaId = T.teaId and acheve.state>4 and T.direction=?";
		String[] parameter={String.valueOf(teacher.getDirection())};
		MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> lists=mysqlcurd.SelectData(sql,parameter);
		Map map=lists.get(0);
		try {
			sum=Integer.parseInt(map.get(0).toString());
		} catch (NumberFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sum;
	}	
	
	
	
	
	
	
	/**
	 * @category 得到一个方向项目的所有经费总和
	 * @author laohe
	 * return sum
	 */
	public int getCountOfProjectFunds(Teacher teacher)
	
	{ 
		int sumFunds=0;
		String sql="select sum(funds) from project as pr,teacher as t where t.teaId=pr.teaId and t.direction=?";	
		String[] parameter={String.valueOf(teacher.getDirection())};
		MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> lists=mysqlcurd.SelectData(sql,parameter);
		Map map=lists.get(0);
		try {
			if(map.get(0)!=null)
			{
				sumFunds=Integer.parseInt(map.get(0).toString());
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return sumFunds;
	}
	/**
	 * @category 得到一个方向项目的到账经费总和
	 * @author 贺智远
	 * @param teacher
	 * @return sum
	 */
	public int getCountOfProjectArriveFunds(Teacher teacher)
	{
		int sum=0;
		String sql="select sum(arriveFunds) from project as pr ,teacher as t where pr.teaId=t.teaId and t.direction=?";
		String[] parameter ={ String.valueOf(teacher.getDirection())};
		 MysqlCrud mysqlcrud=new MysqlCrud();
		 List<Map> lists=mysqlcrud.SelectData(sql, parameter);
		 Map map=lists.get(0);
		 System.out.println("fdsafdsafdsa"+map.get(0));
		 try {
			 if(map.get(0)!=null)
				{
					sum=Integer.parseInt(map.get(0).toString());
				}
		} catch (NumberFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		return sum;
	}
	
	/**
	 * @category 得到一个方向论著类型为？（1,2,3,4）的所有数量
	 * @author 老贺
	 * @return sum
	 */

		public int getCountOfTreatiseClass(int i,Teacher teacher)
	{
		int sum=0;
	      String sql = "select count(*) from treatise as tr,teacher as t where tr.teaId=t.teaId and tr.state>4 and tr.class=? and t.direction=?";
		String[] parameter ={ String.valueOf(i),String.valueOf(teacher.getDirection())};
		MysqlCrud mysqlcrud =new MysqlCrud();	
		List<Map> lists=mysqlcrud.SelectData(sql, parameter);
		 Map map = lists.get(0);
		 sum=Integer.parseInt(map.get(0).toString());	
		return sum;	
	}		
		
		/**
		 * @category 得到一个方向的所有论著  的数量
		 * @author chenkai
		 * @return sum int 教师数量
		 */
		public int getCountOfDirectTreatise(Teacher teacher) {

			int sum=0;
			String sql="select count(*) from treatise as acheve ,teacher as T where acheve.teaId = T.teaId and acheve.state>4 and T.direction=?";
			String[] parameter={String.valueOf(teacher.getDirection())};
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> lists=mysqlcurd.SelectData(sql,parameter);
			Map map=lists.get(0);
			sum=Integer.parseInt(map.get(0).toString());
			return sum;
		}
		
		/**
		 * @category 得到一个方向的所有论文的数量
		 * @author chenkai
		 * @return sum int 教师数量
		 */
		public int getCountOfDirectPaper(Teacher teacher){
			int sum=0;
			String sql="select count(*) from paper as acheve ,teacher as T where acheve.teaId = T.teaId and acheve.state>4 and T.direction=?";
			String[] parameter={String.valueOf(teacher.getDirection())};
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> lists=mysqlcurd.SelectData(sql,parameter);
			Map map=lists.get(0);
			sum=Integer.parseInt(map.get(0).toString());
			return sum;
		}
		
		/** @category 得到一个方向的收录类型为？(1,2,3)的论文数量
		 * author老贺
		 * return sum
		 */
		public int getCountOfPaperInclude(int i,Teacher teacher)
		{
			int sum=0;
			String sql="select count(*) from paper as pa,teacher as t where pa.teaId = t.teaId and pa.include=? and pa.state>4 and t.direction =?";
			String[] parameter= {String.valueOf(i),String.valueOf(teacher.getDirection())};
			MysqlCrud mysqlcurd = new MysqlCrud();
			List<Map> lists=mysqlcurd.SelectData(sql,parameter);
			Map map=lists.get(0);
			sum=Integer.parseInt(map.get(0).toString());		
			return sum;
			
		}
		
		/**
		 * @category 得到一个方向的所有专利的数量
		 * @author chenkai
		 * @return sum int 教师数量
		 */
		public int getCountOfDirectPatent(Teacher teacher) {
			int sum=0;
			String sql="select count(*) from patent as acheve ,teacher as T where acheve.teaId = T.teaId and acheve.examine>4 and T.direction=?";
			String[] parameter={String.valueOf(teacher.getDirection())};
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> lists=mysqlcurd.SelectData(sql,parameter);
			Map map=lists.get(0);
			sum=Integer.parseInt(map.get(0).toString());
			return sum;
		}
		
		/**
		 * @category 专利类别分别为0.1,2，？的数量
		 * @author laohe
		 */
		public int getCountOfPatentClass(Teacher teacher,int i)
		{
			int sum=0;
			String sql="select count(*) from patent ,teacher where patent.teaId=teacher.teaId and patent.examine>4 and teacher.direction=? and patent.expClass=?";
			String[] parameter={String.valueOf(teacher.getDirection()),String.valueOf(i)};
			MysqlCrud mysqlcrud =new MysqlCrud();			
			List<Map> list=mysqlcrud.SelectData(sql, parameter);
			Map map=list.get(0);
			sum=Integer.parseInt(map.get(0).toString());
			return sum;
		}
		/**
		 * @category 搜索专家类别，名字
		 * @author 老贺
		 */
		public void getClassName(Teacher teacher)
		{
			String sql="select expertclass.expert,teacher.name from expertclass  ,teacher  where teacher.teaId=expertclass.expId and teacher.direction=?";
			String[] parameter={String.valueOf(teacher.getDirection())};
			MysqlCrud mysqlcrud=new MysqlCrud();
			List<Map> lists=mysqlcrud.SelectData(sql, parameter);
			for(int i=0;i<lists.size();i++)
			{
				Map map=lists.get(i);
				
				System.out.print(map.get(1));
				System.out.print("  ");
				
				
				
				System.out.println(map.get(0));
			}
			
		
		
			
		}

}
